<?php


namespace app\admin\controller;


use app\admin\library\Backup;
use app\BaseController;
use app\Request;
use think\Exception;
use think\facade\Config;
use think\facade\Db;
use think\facade\Env;
use think\facade\View;
use think\trace\TraceDebug;

class Database extends BaseController
{


    public function query(Request $request)
    {
        $do_action = $request->param('do_action');
        echo '<style type="text/css">
            xmp,body{margin:0;padding:0;line-height:18px;font-size:12px;font-family:"Helvetica Neue", Helvetica, Microsoft Yahei, Hiragino Sans GB, WenQuanYi Micro Hei, sans-serif;}
            hr{height:1px;margin:5px 1px;background:#e3e3e3;border:none;}
            </style>';
        if ($do_action == '') {
            exit(('未知参数'));
        }
        $tablename = $request->param("tablename/a");
        if (in_array($do_action, array('doquery', 'optimizeall', 'repairall'))) {
            $this->$do_action();
        } elseif ($tablename == null) {
            exit(('未知参数'));
        } else {
            foreach ($tablename as $table) {
                $this->$do_action($table);
                echo "<br />";
            }
        }
    }

    private function viewinfo($name)
    {
        $row = Db::query("SHOW CREATE TABLE `{$name}`");
        $row = array_values($row[0]);
        $info = $row[1];
        echo "<xmp>{$info};</xmp>";
    }

    private function viewdata($name = '')
    {
        $sqlquery = "SELECT * FROM `{$name}`";
        $this->doquery($sqlquery);
    }

    private function optimize($name = '')
    {
        if (Db::execute("OPTIMIZE TABLE `{$name}`")) {
            echo __('优化表 %s 成功', $name);
        } else {
            echo __('优化表 %s 失败', $name);
        }
    }

    private function optimizeall($name = '')
    {
        $list = Db::query("SHOW TABLES");
        foreach ($list as $key => $row) {
            $name = reset($row);
            if (Db::execute("OPTIMIZE TABLE {$name}")) {
                echo __('优化表 %s 成功', $name);
            } else {
                echo __('优化表 %s 失败', $name);
            }
            echo "<br />";
        }
    }

    private function repair($name = '')
    {
        if (Db::execute("REPAIR TABLE `{$name}`")) {
            echo __('修复表 %s 成功', $name);
        } else {
            echo __('修复表 %s 失败', $name);
        }
    }

    private function repairall($name = '')
    {
        $list = Db::query("SHOW TABLES");
        foreach ($list as $key => $row) {
            $name = reset($row);
            if (Db::execute("REPAIR TABLE {$name}")) {
                echo __('修复表 %s 成功', $name);
            } else {
                echo __('修复表 %s 失败', $name);
            }
            echo "<br />";
        }
    }

    private function doquery($sql = null)
    {

        $sqlquery = $sql ? $sql : $this->request->post('sqlquery');

        if ($sqlquery == '') {
            exit(__('SQL can not be empty'));
        }
        $sqlquery = str_replace('__PREFIX__', config('database.prefix'), $sqlquery);
        $sqlquery = str_replace("\r", "", $sqlquery);
        $sqls = preg_split("/;[ \t]{0,}\n/i", $sqlquery);
        $maxreturn = 100;
        $r = '';
        foreach ($sqls as $key => $val) {
            if (trim($val) == '') {
                continue;
            }
            $val = rtrim($val, ';');
            $r .= "SQL：<span style='color:green;'>{$val}</span> ";
            if (preg_match("/^(select|explain)(.*)/i ", $val)) {

                $limit = stripos(strtolower($val), "limit") !== false ? true : false;
                $aa = explode(';', $val);
                foreach ($aa as $k => $v1) {
                    $count = Db::execute($v1);
                    if ($count > 0) {
                        $resultlist = Db::query($v1 . (!$limit && $count > $maxreturn ? ' LIMIT ' . $maxreturn : ''));
                    } else {
                        $resultlist = [];
                    }
                    $time = time();
                    $usedseconds = __('Query took %s seconds', $time) . "<br />";
                    $r .= "<hr/>";
                    if ($count <= 0) {
                        $r .= __('Query returned an empty result');
                    } else {
                        $r .= (__('Total:%s', $count) . (!$limit && $count > $maxreturn ? ',' . __('Max output:%s', $maxreturn) : ""));
                    }
                    $r = $r . ',' . $usedseconds;
                    $j = 0;
                    foreach ($resultlist as $m => $n) {
                        $j++;
                        if (!$limit && $j > $maxreturn) {
                            break;
                        }
                        $r .= "<hr/>";
                        $r .= "<font color='red'>" . __('Row:%s', $j) . "</font><br />";
                        foreach ($n as $k => $v) {
                            $r .= "<font color='blue'>{$k}：</font>{$v}<br/>\r\n";
                        }
                    }
                }

            } else {

                $count = Db::getPdo()->exec($val);


                $r .= __('Query affected %s rows and took %s seconds', $count, time()) . "<br />";
            }
        }
        echo $r;
    }

    public function backuplist(Request $request)
    {
        $config = Config::get('database.backup');

        $backupDir = app_path() . 'public' . DIRECTORY_SEPARATOR . $config['backupDir'];

        $backuplist = [];
        foreach (glob($backupDir . "*.zip") as $filename) {
            $time = filemtime($filename);
            $backuplist[$time] =
                [
                    'file' => str_replace($backupDir, '', $filename),
                    'date' => date("Y-m-d H:i:s", $time),
                    'size' => format_bytes(filesize($filename))
                ];
        }
        krsort($backuplist);
        $this->success("", null, ['backuplist' => array_values($backuplist)]);
    }


    /**
     * 备份
     */
    public function backup(Request $request)
    {
        $config = Config::get('database.backup');

        $backupDir = app_path() . 'public' . DIRECTORY_SEPARATOR . $config['backupDir'];

        if ($request->isAjax()) {

            if (!class_exists('ZipArchive')) {
                $this->error("服务器缺少php-zip组件，无法进行备份操作");
            }
            $database = Config::get('database.connections.mysql');

            try {
                $backup = new Backup($database['hostname'], $database['username'], $database['database'], $database['password'], $database['hostport']);


                $backup->setIgnoreTable($config['backupIgnoreTables'])->backup($backupDir);
            } catch (Exception $e) {
                $this->error($e->getMessage());
            }
            $this->success(__('Backup successful'));
        }
        return;
    }
}